import mysql.connector
from mysql.connector import Error
from entities import *

class Database:
    def __init__(self, host='localhost', user='root', password='123456', database='qwert_poetry'):
        self.connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )

    def create_user(self, user: User):
        sql = "INSERT INTO User (nickname, email, phone_number, password_hash) VALUES (%s, %s, %s, %s)"
        values = (user.nickname, user.email, user.phone_number, user.password_hash)
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, values)
        self.connection.commit()
        user.user_id = cursor.lastrowid
        return user

    def read_user(self, user_id: int) -> Optional[User]:
        sql = "SELECT * FROM User WHERE user_id = %s"
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, (user_id,))
        result = cursor.fetchone()
        if result:
            return User(*result)
        return None

    def update_user(self, user: User):
        sql = "UPDATE User SET nickname = %s, email = %s, phone_number = %s, password_hash = %s WHERE user_id = %s"
        values = (user.nickname, user.email, user.phone_number, user.password_hash, user.user_id)
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, values)
        self.connection.commit()

    def delete_user(self, user_id: int):
        sql = "DELETE FROM User WHERE user_id = %s"
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, (user_id,))
        self.connection.commit()

    # Similarly create methods for other entities
    # For example, create, read, update, delete for Poem

    def create_poem(self, poem: Poem):
        sql = "INSERT INTO Poem (title, author_id, content, translation, annotation, appreciation) VALUES (%s, %s, %s, %s, %s, %s)"
        values = (poem.title, poem.author_id, poem.content, poem.translation, poem.annotation, poem.appreciation)
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, values)
        self.connection.commit()
        poem.poem_id = cursor.lastrowid
        return poem

    def read_poem(self, poem_id: int) -> Optional[Poem]:
        sql = "SELECT * FROM Poem WHERE poem_id = %s"
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, (poem_id,))
        result = cursor.fetchone()
        if result:
            return Poem(*result)
        return None

    # Similarly create methods for other entities
    # author
    def create_author(self, author: Author):
        sql = "INSERT INTO Author (name, dynasty, `desc`) VALUES (%s, %s, %s)"
        values = (author.name, author.dynasty, author.desc)
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, values)
        self.connection.commit()
        author.author_id = cursor.lastrowid
        return author

    # tag
    def create_tag(self, tag: Tag):
        sql = "INSERT INTO Tag (name) VALUES (%s)"
        values = (tag.name,)
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, values)
        self.connection.commit()
        tag.tag_id = cursor.lastrowid
        return tag

    def find_tag_by_name(self, tag_name: str):
        sql = "SELECT * FROM Tag WHERE name = %s"
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, (tag_name,))
        result = cursor.fetchone()
        if result:
            return Tag(*result)
        return None

    def create_poem_tag(self, poem_tag: PoemTag):
        sql = "INSERT IGNORE Poem_Tag (poem_id, tag_id) VALUES (%s, %s)"
        values = (poem_tag.poem_id, poem_tag.tag_id)
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, values)
        self.connection.commit()
        return poem_tag

    def read_author_by_name(self, author_name: str):
        sql = "SELECT * FROM Author WHERE name = %s"
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, (author_name,))
        result = cursor.fetchone()
        if result:
            return Author(*result)
        return None

    def read_poem_by_title(self, title: str):
        sql = "SELECT * FROM Poem WHERE title = %s"
        cursor = self.connection.cursor(buffered=True)
        cursor.execute(sql, (title,))
        result = cursor.fetchone()
        if result:
            return Poem(*result)
        return None

db = None
if db is None:
    db = Database()

